Skip to main content

Analytics

Build and run custom queries to analyze your application data.

Overview​

Analytics provides a query builder interface to create custom SQL queries against your application data. Build reports, analyze trends, and export data for further analysis.

Concepts​

Query​

A saved SQL query that can be executed to retrieve data from your app's database tables.

Connection​

A database connection or secret that provides credentials for query execution.

Parameters​

Dynamic values that can be passed to queries at runtime, allowing reusable queries with different inputs.

View Queries​

  1. Navigate to your App in the Dashboard.
  2. Click Analytics in the sidebar.
  3. View all saved queries with:
    • Query Name: Display name
    • Description: Query purpose
    • Created: When the query was created
    • Last Run: Most recent execution

Create a Query​

  1. Navigate to Analytics in your app.
  2. Click Create Query.
  3. Fill in query details:
    • Name: Descriptive query name
    • Description: Purpose of the query
    • Connection: Select data connection/secret
  4. Write your SQL query.
  5. Click Save.

Query Editor​

The query editor provides a full-featured SQL editing environment:

Writing Queries​

SELECT
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent
FROM orders
WHERE created_at > :start_date
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 100;

Using Parameters​

Define parameters with colon syntax:

SELECT * FROM products
WHERE category = :category
AND price <= :max_price;

Parameters appear in the parameter form when executing.

Execute a Query​

  1. Navigate to the query.
  2. Fill in any required parameters.
  3. Click Execute.
  4. View results in the data table:
    • Column headers from query
    • Result rows
    • Row count

View Query Results​

Results are displayed in a DataTable with:

FeatureDescription
SortingClick column headers to sort
PaginationNavigate through large result sets
Column ResizeAdjust column widths

Export Results​

Export query results to files:

Export to CSV​

  1. Run the query.
  2. Click Export.
  3. Select CSV.
  4. Download the file.

Export to Excel​

  1. Run the query.
  2. Click Export.
  3. Select Excel.
  4. Download the file.

Edit a Query​

  1. Navigate to Analytics in your app.
  2. Click on the query to open it.
  3. Modify the SQL or settings.
  4. Click Save.

Delete a Query​

  1. Navigate to Analytics in your app.
  2. Click the Delete (trash) icon on the query.
  3. Confirm deletion.

Data Connections​

Select Connection​

Queries run against a specific data connection:

  1. When creating/editing a query, select Connection.
  2. Choose from available connections or secrets.
  3. The query will use those credentials to connect.

Available Connections​

  • Default: Your app's primary database
  • Secrets: External databases configured in site secrets

Query Examples​

Count Records​

SELECT COUNT(*) as total_users FROM users;

Aggregate Data​

SELECT
    DATE(created_at) as date,
    COUNT(*) as signups
FROM users
WHERE created_at >= :start_date
GROUP BY DATE(created_at)
ORDER BY date DESC;

Join Tables​

SELECT
    u.name,
    u.email,
    COUNT(o.id) as orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
ORDER BY orders DESC;

Parameterized Query​

SELECT * FROM products
WHERE category = :category
AND status = 'active'
ORDER BY name;

Configuration​

Query Fields​

FieldDescriptionRequired
NameDisplay nameYes
DescriptionQuery purposeNo
ConnectionData connectionYes
SQLQuery textYes

Limits​

ResourceLimit
Queries per app100
Result rows10,000
Query timeout30 seconds
Export size50 MB
info

Need higher limits? Contact support to discuss your requirements.

Troubleshooting​

Query returns error​

Problem: Query execution fails with a SQL error.

Solution:

  1. Check SQL syntax is correct.
  2. Verify table and column names exist.
  3. Ensure parameter placeholders match inputs.
  4. Check the connection has access to the tables.

Query times out​

Problem: Query takes too long and times out.

Solution:

  1. Add LIMIT clause to reduce result size.
  2. Add indexes on filtered columns.
  3. Optimize the query with smaller date ranges.
  4. Break into multiple smaller queries.

No results returned​

Problem: Query returns empty results.

Solution:

  1. Verify the WHERE conditions match data.
  2. Check parameter values are correct.
  3. Test with broader conditions first.
  4. Confirm data exists in the tables.

Last Updated: January 2025